select @documentFolderType:=FolderTypeId from FolderType where Name='Document';
select @docTypeFolderType:=FolderTypeId from FolderType where Name='DocType';

select @rootDocumentFolder:=FolderId from Folder where FolderType=@documentFolderType and IsNull(ParentFolder);
select @rootDocTypeFolder:=FolderId from Folder where FolderType=@docTypeFolderType AND IsNull(ParentFolder);

select @userRole:=DcRoleId from DcRole where RoleName='Users';

select @docCircAdmin:=DcUserId from DcUser where Login='DocCircAdmin';
select @docCircUser1:=DcUserId from DcUser where Login='DocCircUser1';
select @docCircUser2:=DcUserId from DcUser where Login='DocCircUser2';
select @docCircUser3:=DcUserId from DcUser where Login='DocCircUser3';

select @notificationRIT:=RouteItemTypeId from RouteItemType where Name='Notification';
select @decisionRIT:=RouteItemTypeId from RouteItemType where Name='Decision';

select @notSavedDS:=DocStatusId from DocStatus where Name='NotSavedDocStatus';
select @draftDS:=DocStatusId from DocStatus where Name='DraftDocStatus';
select @underConsDS:=DocStatusId from DocStatus where Name='UnderConsDocStatus';
select @finalizedDS:=DocStatusId from DocStatus where Name='FinalizedDocStatus';

-- Add DocType: DocType1 "Test type first" into rootDocTypeFolder by DocCircAdmin
insert into DocType (DocTypeId, Folder, Name, Description, Prefix, NextNumber, IsNew, DcUser) values 
(default, @rootDocTypeFolder, 'Test type first', 'first type', 'TTF', 1, false, @docCircAdmin); 
set @docType1:=last_insert_id(); 
--
	insert into RoutePoint (DocType, OrderNum, Expression) Values
		(@docType1, 1, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType1, @docCircUser1, @decisionRIT, 0);
--
insert into DocTypePermission (DocType, DcRole, C, R, U, D, RO, UO, DO)
	select @docType1, DcRole, C, R, U, D, RO, UO, DO from NewDocTypePermission;
	
	
-- Add DocType: DocType2 "Test type second" into rootDocTypeFolder by DocCircAdmin
insert into DocType (DocTypeId, Folder, Name, Description, Prefix, NextNumber, IsNew, DcUser) values 
(default, @rootDocTypeFolder, 'Test type second', 'second type', 'TTS', 1, false, @docCircAdmin); 
set @docType2:=last_insert_id(); 
--
	insert into RoutePoint (DocType, OrderNum, Expression) Values
		(@docType2, 1, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType2, @docCircUser1, @decisionRIT, 0);
	set @lastRouteItem:=last_insert_id();
--
insert into DocTypePermission (DocType, DcRole, C, R, U, D, RO, UO, DO)
	select @docType2, DcRole, C, R, U, D, RO, UO, DO from NewDocTypePermission;	
	
-- Add new document folder into the root doc folder: Test document folder1
insert into Folder (ParentFolder, Name, FolderType) 
	Values (@rootDocumentFolder, 'Test document folder1', @documentFolderType);
set @testDocumentFolder1:=last_insert_id();
insert into FolderPermission (DcRole, Folder, C,R,U,D,B,RO,UO,DO)
	select DcRole, @testDocumentFolder1, C,R,U,D,B,RO,UO,DO from NewFolderPermission 
	where FolderType=@documentFolderType;

-- Add DocCard: DocCard1 "Test DocCard1" into rootDocumentFolder by DocCircAdmin
-- under consideration by User3, passed User1, User2 RoutePointNum=2
insert into DocCard 
	(Folder, DocStatus, RegisteringUser, DcUser, Subject, InternalNumber, 
	RegistrationTime, AttachmentsAreChangeable, OriginalNumber, CustomSendingCompany, CustomReceivingCompany, 
    IsNew, RoutePointNum,
    DocType, LastMovement, OriginalDate, Note, SendingCompany, ReceivingCompany) Values 
    (@rootDocumentFolder, @draftDS, @docCircAdmin, @docCircAdmin, 'Test DocCard1', 'TTF000001', 
    '2007-05-24 11:35:00', false, 'T21', 'MS', 'Evpo', 
    false, 2,
    @docType1, '2007-05-23 12:00:00', '2007-05-01', 'this is the note of DocCard1', null, null
    );

set @docCard1:=last_insert_id();

-- Add DocCard: DocCard2 "Test DocCard2" into testDocumentFolder1 by DocCircUser2
-- under consideration by User2, passed User1 RoutePointNum=2
insert into DocCard 
	(Folder, DocStatus, RegisteringUser, DcUser, Subject, InternalNumber, 
	RegistrationTime, AttachmentsAreChangeable, OriginalNumber, CustomSendingCompany, CustomReceivingCompany, 
    IsNew, RoutePointNum,
    DocType, LastMovement, OriginalDate, Note, SendingCompany, ReceivingCompany) Values 
    (@testDocumentFolder1, @draftDS, @docCircAdmin, @docCircUser2, 'Test DocCard2', 'TTS000002', 
    '2007-02-24 11:35:00', false, 'T232', 'MS2', 'Evpo2', 
    false, 2,
    @docType2, '2007-02-23 12:00:00', '2007-02-01', 'this is the note of DocCard2', null, null
    );

set @docCard2:=last_insert_id();

-- Add DocCard: DocCard3 "Test document card" into rootDocumentFolder by DocCircAdmin
-- under consideration by User1 RoutePointNum=1
insert into DocCard 
	(Folder, DocStatus, RegisteringUser, DcUser, Subject, InternalNumber, 
	RegistrationTime, AttachmentsAreChangeable, OriginalNumber, CustomSendingCompany, CustomReceivingCompany, 
    IsNew, RoutePointNum,
    DocType, LastMovement, OriginalDate, Note, SendingCompany, ReceivingCompany) Values 
    (@rootDocumentFolder, @draftDS, @docCircUser2, @docCircAdmin, 'Test DocCard3', 'TTF000003', 
    '2007-03-24 11:35:00', false, 'T213', 'MS3', 'Evpo3', 
    false, 1,
    @docType1, '2007-03-23 12:00:00', '2007-03-01', 'this is the note of DocCard3', null, null
    );

set @docCard3:=last_insert_id();